package com.lr.excel.utils;


import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.util.Random;
import java.util.UUID;

/**
 * @author liurui
 * @ad Java一对一辅导，手把手带你拿高薪！
 * @WeChat：liuliang70
 * @date 2024/6/13 15:31
 */
public class POIExcelUtils {
    public static void main(String[] args) {

        SXSSDataInput();
    }

    /**
     *  流式处理方式写入数据到Excel
     */
    private static void SXSSDataInput() {
        String excelFilePath = "million_rows_data.xlsx";
        long l = System.currentTimeMillis();
        try {
            // 保持在内存中的行数，这里设置为1000
            Workbook workbook = new SXSSFWorkbook();
            FileOutputStream outputStream = new FileOutputStream(excelFilePath);
            Sheet sheet = workbook.createSheet("Data");
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("UUID");
            for (int i = 1; i <= 20; i++) {
                headerRow.createCell(i).setCellValue("RandomText" + i);
            }
            Random random = new Random();
            for (int i = 1; i <= 10; i++) {
                Row row = sheet.createRow(i);
                row.createCell(0).setCellValue(UUID.randomUUID().toString());
                StringBuilder sb = new StringBuilder();
                for (int j = 0; j < 10; j++) {
                    char randomChar = (char) (random.nextInt(26) + 'A');
                    sb.append(randomChar);
                }
                for (int j = 1; j <= 20; j++) {
                    row.createCell(j).setCellValue(sb.toString());
                }

            }
            workbook.write(outputStream);
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("总耗时：" + (System.currentTimeMillis() - l));
    }

    /**
     * 最基本的POI方式写入数据到Excel
     */
    private static void XssDataInput() {
        String excelFilePath = "million_rows_data.xlsx";
        long l = System.currentTimeMillis();
        try {
            Workbook workbook = new XSSFWorkbook();
            FileOutputStream outputStream = new FileOutputStream(excelFilePath);
            Sheet sheet = workbook.createSheet("Data");
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("UUID");
            for (int i = 1; i <= 2; i++) {
                headerRow.createCell(i).setCellValue("RandomText" + i);
            }
            Random random = new Random();
            for (int i = 1; i <= 10; i++) {
                Row row = sheet.createRow(i);
                row.createCell(0).setCellValue(UUID.randomUUID().toString());
                StringBuilder sb = new StringBuilder();
                for (int j = 0; j < 10; j++) {
                    char randomChar = (char) (random.nextInt(26) + 'A');
                    sb.append(randomChar);
                }
                for (int j = 1; j <= 20; j++) {
                    row.createCell(j).setCellValue(sb.toString());
                }
            }
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("总耗时：" + (System.currentTimeMillis() - l));
    }


}
